{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "# 处理表格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "指标数据\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cname</th>\n",
       "      <th>dotcount</th>\n",
       "      <th>exp</th>\n",
       "      <th>ifshowcode</th>\n",
       "      <th>memo</th>\n",
       "      <th>name</th>\n",
       "      <th>nodesort</th>\n",
       "      <th>sortcode</th>\n",
       "      <th>tag</th>\n",
       "      <th>unit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A010101</th>\n",
       "      <td>地级区划数</td>\n",
       "      <td>0</td>\n",
       "      <td>指地级行政单位即介于省级和县级之间的一级地方行政区域的个数，包括地区、自治州、行政区和盟。</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A010102</th>\n",
       "      <td>地级市数</td>\n",
       "      <td>0</td>\n",
       "      <td>市是省、自治区内人口较集中，政治、经济、文化等方面较重要的城市。市人民政府为一级地方行政组织...</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>地级市数</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A010103</th>\n",
       "      <td>县级区划数</td>\n",
       "      <td>0</td>\n",
       "      <td>县级行政单位指中国地方二级行政区域，是地方政权的基础。县级行政单位包括县、自治县、旗、自治旗...</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>县级区划数</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A010104</th>\n",
       "      <td>市辖区数</td>\n",
       "      <td>0</td>\n",
       "      <td>市辖区（简称区）城市基层政权组织的行政区域。直辖市和较大的市多将市区范围划分为若干区，设立区...</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>市辖区数</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A010105</th>\n",
       "      <td>县级市数</td>\n",
       "      <td>0</td>\n",
       "      <td>县级市是中国大陆行政区划名称，行政地位与县相同的县级行政区</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>县级市数</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A0S0B01</th>\n",
       "      <td>城乡居民社会养老保险参保人数</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>城乡居民社会养老保险参保人数</td>\n",
       "      <td>1</td>\n",
       "      <td>3355</td>\n",
       "      <td></td>\n",
       "      <td>万人</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A0S0B02</th>\n",
       "      <td>城乡居民社会养老保险实际领取待遇人数</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>城乡居民社会养老保险实际领取待遇人数</td>\n",
       "      <td>1</td>\n",
       "      <td>3356</td>\n",
       "      <td></td>\n",
       "      <td>万人</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A0S0B03</th>\n",
       "      <td>城乡居民社会养老保险基金收入</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>城乡居民社会养老保险基金收入</td>\n",
       "      <td>1</td>\n",
       "      <td>3357</td>\n",
       "      <td></td>\n",
       "      <td>亿元</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A0S0B04</th>\n",
       "      <td>城乡居民社会养老保险基金支出</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>城乡居民社会养老保险基金支出</td>\n",
       "      <td>1</td>\n",
       "      <td>3358</td>\n",
       "      <td></td>\n",
       "      <td>亿元</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A0S0B05</th>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>1</td>\n",
       "      <td>3359</td>\n",
       "      <td></td>\n",
       "      <td>亿元</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2943 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      cname  dotcount  \\\n",
       "code                                    \n",
       "A010101               地级区划数         0   \n",
       "A010102                地级市数         0   \n",
       "A010103               县级区划数         0   \n",
       "A010104                市辖区数         0   \n",
       "A010105                县级市数         0   \n",
       "...                     ...       ...   \n",
       "A0S0B01      城乡居民社会养老保险参保人数         1   \n",
       "A0S0B02  城乡居民社会养老保险实际领取待遇人数         1   \n",
       "A0S0B03      城乡居民社会养老保险基金收入         1   \n",
       "A0S0B04      城乡居民社会养老保险基金支出         1   \n",
       "A0S0B05      城乡居民社会养老保险累计结余         1   \n",
       "\n",
       "                                                       exp  ifshowcode memo  \\\n",
       "code                                                                          \n",
       "A010101      指地级行政单位即介于省级和县级之间的一级地方行政区域的个数，包括地区、自治州、行政区和盟。       False        \n",
       "A010102  市是省、自治区内人口较集中，政治、经济、文化等方面较重要的城市。市人民政府为一级地方行政组织...       False        \n",
       "A010103  县级行政单位指中国地方二级行政区域，是地方政权的基础。县级行政单位包括县、自治县、旗、自治旗...       False        \n",
       "A010104  市辖区（简称区）城市基层政权组织的行政区域。直辖市和较大的市多将市区范围划分为若干区，设立区...       False        \n",
       "A010105                      县级市是中国大陆行政区划名称，行政地位与县相同的县级行政区       False        \n",
       "...                                                    ...         ...  ...   \n",
       "A0S0B01                                                          False        \n",
       "A0S0B02                                                          False        \n",
       "A0S0B03                                                          False        \n",
       "A0S0B04                                                          False        \n",
       "A0S0B05                                                          False        \n",
       "\n",
       "                       name  nodesort  sortcode tag unit  \n",
       "code                                                      \n",
       "A010101               地级区划数         1         2        个  \n",
       "A010102                地级市数         1         3        个  \n",
       "A010103               县级区划数         1         4        个  \n",
       "A010104                市辖区数         1         5        个  \n",
       "A010105                县级市数         1         6        个  \n",
       "...                     ...       ...       ...  ..  ...  \n",
       "A0S0B01      城乡居民社会养老保险参保人数         1      3355       万人  \n",
       "A0S0B02  城乡居民社会养老保险实际领取待遇人数         1      3356       万人  \n",
       "A0S0B03      城乡居民社会养老保险基金收入         1      3357       亿元  \n",
       "A0S0B04      城乡居民社会养老保险基金支出         1      3358       亿元  \n",
       "A0S0B05      城乡居民社会养老保险累计结余         1      3359       亿元  \n",
       "\n",
       "[2943 rows x 10 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "地区数据\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>i</th>\n",
       "      <th>dbcode</th>\n",
       "      <th>exp</th>\n",
       "      <th>id</th>\n",
       "      <th>isParent</th>\n",
       "      <th>name</th>\n",
       "      <th>open</th>\n",
       "      <th>pid</th>\n",
       "      <th>wd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>110000</td>\n",
       "      <td>True</td>\n",
       "      <td>北京市</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>120000</td>\n",
       "      <td>True</td>\n",
       "      <td>天津市</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>130000</td>\n",
       "      <td>True</td>\n",
       "      <td>河北省</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>140000</td>\n",
       "      <td>True</td>\n",
       "      <td>山西省</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>150000</td>\n",
       "      <td>True</td>\n",
       "      <td>内蒙古自治区</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>7</td>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>610000</td>\n",
       "      <td>True</td>\n",
       "      <td>陕西省</td>\n",
       "      <td>False</td>\n",
       "      <td>900003</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>8</td>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>620000</td>\n",
       "      <td>True</td>\n",
       "      <td>甘肃省</td>\n",
       "      <td>False</td>\n",
       "      <td>900003</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>9</td>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>630000</td>\n",
       "      <td>True</td>\n",
       "      <td>青海省</td>\n",
       "      <td>False</td>\n",
       "      <td>900003</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>10</td>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>640000</td>\n",
       "      <td>True</td>\n",
       "      <td>宁夏回族自治区</td>\n",
       "      <td>False</td>\n",
       "      <td>900003</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>11</td>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>650000</td>\n",
       "      <td>True</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>False</td>\n",
       "      <td>900003</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>133 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      i dbcode exp      id  isParent      name   open     pid   wd\n",
       "0     0   fsnd      110000      True       北京市  False  100001  reg\n",
       "1     1   fsnd      120000      True       天津市  False  100001  reg\n",
       "2     2   fsnd      130000      True       河北省  False  100001  reg\n",
       "3     3   fsnd      140000      True       山西省  False  100001  reg\n",
       "4     4   fsnd      150000      True    内蒙古自治区  False  100001  reg\n",
       "..   ..    ...  ..     ...       ...       ...    ...     ...  ...\n",
       "128   7   fsnd      610000      True       陕西省  False  900003  reg\n",
       "129   8   fsnd      620000      True       甘肃省  False  900003  reg\n",
       "130   9   fsnd      630000      True       青海省  False  900003  reg\n",
       "131  10   fsnd      640000      True   宁夏回族自治区  False  900003  reg\n",
       "132  11   fsnd      650000      True  新疆维吾尔自治区  False  900003  reg\n",
       "\n",
       "[133 rows x 9 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "总数据\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zb</th>\n",
       "      <th>reg</th>\n",
       "      <th>sj</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">A010101</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">110000</th>\n",
       "      <th>2018</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">A0S0B05</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">650000</th>\n",
       "      <th>2013</th>\n",
       "      <td>33.856600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>24.914044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>908300 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          data\n",
       "zb      reg    sj             \n",
       "A010101 110000 2018        NaN\n",
       "               2017        NaN\n",
       "               2016        NaN\n",
       "               2015        NaN\n",
       "               2014        NaN\n",
       "...                        ...\n",
       "A0S0B05 650000 2013  33.856600\n",
       "               2012  24.914044\n",
       "               2011        NaN\n",
       "               2010        NaN\n",
       "               2009        NaN\n",
       "\n",
       "[908300 rows x 1 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_指标=pd.read_csv(\"fsnd_zb_meta.tsv\",encoding='utf8',sep='\\t',keep_default_na=False,na_values='na_rep',index_col=[0])\n",
    "print('指标数据')\n",
    "display(df_指标)\n",
    "\n",
    "df_地区=pd.read_csv(\"reg_treeId_level2.tsv\",encoding='utf8',sep='\\t',keep_default_na=False,na_values='na_rep')\n",
    "print('地区数据')\n",
    "display(df_地区)\n",
    "\n",
    "df_总数据=pd.read_csv(\"fsnd_zb_data.tsv\",encoding='utf8',sep='\\t',keep_default_na=False,na_values='na_rep',\n",
    "                     index_col=[0,1,2])\n",
    "print('总数据')\n",
    "display(df_总数据)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "指标字典=df_指标['cname'].to_dict()\n",
    "# display(指标字典)\n",
    "地区字典=df_地区.set_index('id')['name'].to_dict()\n",
    "# display(地区字典)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地区</th>\n",
       "      <th>指标</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>北京市</td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>北京市</td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>北京市</td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>北京市</td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2015</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>北京市</td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908295</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2013</td>\n",
       "      <td>33.856600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908296</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2012</td>\n",
       "      <td>24.914044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908297</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2011</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908298</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908299</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2009</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>908300 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              地区              指标     年         数据\n",
       "0            北京市           地级区划数  2018        NaN\n",
       "1            北京市           地级区划数  2017        NaN\n",
       "2            北京市           地级区划数  2016        NaN\n",
       "3            北京市           地级区划数  2015        NaN\n",
       "4            北京市           地级区划数  2014        NaN\n",
       "...          ...             ...   ...        ...\n",
       "908295  新疆维吾尔自治区  城乡居民社会养老保险累计结余  2013  33.856600\n",
       "908296  新疆维吾尔自治区  城乡居民社会养老保险累计结余  2012  24.914044\n",
       "908297  新疆维吾尔自治区  城乡居民社会养老保险累计结余  2011        NaN\n",
       "908298  新疆维吾尔自治区  城乡居民社会养老保险累计结余  2010        NaN\n",
       "908299  新疆维吾尔自治区  城乡居民社会养老保险累计结余  2009        NaN\n",
       "\n",
       "[908300 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 通过修改索引，替换表格中的“代号”为前面创建的字典内容。\n",
    "df1=df_总数据.reset_index().set_index('zb').rename(指标字典)\n",
    "# df1\n",
    "df2=df1.reset_index().set_index('reg').rename(地区字典)\n",
    "# df2\n",
    "\n",
    "# 修改表头，使更直观\n",
    "df_待筛选=df2.reset_index().rename(columns={'reg':'地区','zb':'指标','sj':'年','data':'数据'})\n",
    "df_待筛选"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "# 数据切片"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地区</th>\n",
       "      <th>指标</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19530</th>\n",
       "      <td>北京市</td>\n",
       "      <td>城镇单位就业人员</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19531</th>\n",
       "      <td>北京市</td>\n",
       "      <td>城镇单位就业人员</td>\n",
       "      <td>2017</td>\n",
       "      <td>812.8589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19532</th>\n",
       "      <td>北京市</td>\n",
       "      <td>城镇单位就业人员</td>\n",
       "      <td>2016</td>\n",
       "      <td>791.5197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19533</th>\n",
       "      <td>北京市</td>\n",
       "      <td>城镇单位就业人员</td>\n",
       "      <td>2015</td>\n",
       "      <td>777.3448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19534</th>\n",
       "      <td>北京市</td>\n",
       "      <td>城镇单位就业人员</td>\n",
       "      <td>2014</td>\n",
       "      <td>755.8601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56725</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>2013</td>\n",
       "      <td>46636.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56726</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>2012</td>\n",
       "      <td>45071.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56727</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>2011</td>\n",
       "      <td>39862.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56728</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>2010</td>\n",
       "      <td>35950.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56729</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>2009</td>\n",
       "      <td>31217.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>22940 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             地区                     指标     年          数据\n",
       "19530       北京市               城镇单位就业人员  2018         NaN\n",
       "19531       北京市               城镇单位就业人员  2017    812.8589\n",
       "19532       北京市               城镇单位就业人员  2016    791.5197\n",
       "19533       北京市               城镇单位就业人员  2015    777.3448\n",
       "19534       北京市               城镇单位就业人员  2014    755.8601\n",
       "...         ...                    ...   ...         ...\n",
       "56725  新疆维吾尔自治区  公共管理和社会组织城镇单位就业人员平均工资  2013  46636.0000\n",
       "56726  新疆维吾尔自治区  公共管理和社会组织城镇单位就业人员平均工资  2012  45071.0000\n",
       "56727  新疆维吾尔自治区  公共管理和社会组织城镇单位就业人员平均工资  2011  39862.0000\n",
       "56728  新疆维吾尔自治区  公共管理和社会组织城镇单位就业人员平均工资  2010  35950.0000\n",
       "56729  新疆维吾尔自治区  公共管理和社会组织城镇单位就业人员平均工资  2009  31217.0000\n",
       "\n",
       "[22940 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 通过str.contains()进行字符串的筛选。\n",
    "df_筛选 = df_待筛选[df_待筛选.指标.str.contains(\"城镇单位就业人员\")]\n",
    "df_筛选"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>指标</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>国有城镇单位就业人员工资总额</th>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国有城镇单位就业人员工资总额</th>\n",
       "      <td>2017</td>\n",
       "      <td>2663.84409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国有城镇单位就业人员工资总额</th>\n",
       "      <td>2016</td>\n",
       "      <td>2350.74965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国有城镇单位就业人员工资总额</th>\n",
       "      <td>2015</td>\n",
       "      <td>2106.60123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国有城镇单位就业人员工资总额</th>\n",
       "      <td>2014</td>\n",
       "      <td>1932.38647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国有城镇单位就业人员工资总额</th>\n",
       "      <td>2013</td>\n",
       "      <td>928.67688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国有城镇单位就业人员工资总额</th>\n",
       "      <td>2012</td>\n",
       "      <td>864.53594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国有城镇单位就业人员工资总额</th>\n",
       "      <td>2011</td>\n",
       "      <td>709.78884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国有城镇单位就业人员工资总额</th>\n",
       "      <td>2010</td>\n",
       "      <td>577.50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国有城镇单位就业人员工资总额</th>\n",
       "      <td>2009</td>\n",
       "      <td>489.92515</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>310 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   年          数据\n",
       "指标                              \n",
       "国有城镇单位就业人员工资总额  2018         NaN\n",
       "国有城镇单位就业人员工资总额  2017  2663.84409\n",
       "国有城镇单位就业人员工资总额  2016  2350.74965\n",
       "国有城镇单位就业人员工资总额  2015  2106.60123\n",
       "国有城镇单位就业人员工资总额  2014  1932.38647\n",
       "...              ...         ...\n",
       "国有城镇单位就业人员工资总额  2013   928.67688\n",
       "国有城镇单位就业人员工资总额  2012   864.53594\n",
       "国有城镇单位就业人员工资总额  2011   709.78884\n",
       "国有城镇单位就业人员工资总额  2010   577.50000\n",
       "国有城镇单位就业人员工资总额  2009   489.92515\n",
       "\n",
       "[310 rows x 2 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将\"国有城镇单位就业人员工资总额\"为行索引，进行对想要的数据进行筛选查看。\n",
    "df_城镇工资总=df_待筛选.set_index(\"指标\").loc[\"国有城镇单位就业人员工资总额\",\"年\":\"数据\"]\n",
    "df_城镇工资总"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['城镇单位就业人员', '农林牧渔业城镇单位就业人员', '采矿业城镇单位就业人员', '制造业城镇单位就业人员',\n",
       "       '电力、燃气及水的生产和供应业城镇单位就业人员', '建筑业城镇单位就业人员', '交通运输、仓储及邮电通信业城镇单位就业人员',\n",
       "       '信息传输、计算机服务和软件业城镇单位就业人员', '批发和零售业城镇单位就业人员', '住宿和餐饮业城镇单位就业人员',\n",
       "       '金融业城镇单位就业人员', '房地产业城镇单位就业人员', '租赁和商务服务业城镇单位就业人员',\n",
       "       '科学研究、技术服务和地质勘查业城镇单位就业人员', '水利、环境和公共设施管理业城镇单位就业人员',\n",
       "       '居民服务和其他服务业城镇单位就业人员', '教育业城镇单位就业人员', '卫生、社会保障和社会福利业城镇单位就业人员',\n",
       "       '文化、体育和娱乐业城镇单位就业人员', '公共管理和社会组织城镇单位就业人员', '城镇单位就业人员工资总额',\n",
       "       '国有城镇单位就业人员工资总额', '其他城镇单位就业人员工资总额', '城镇单位就业人员工资总额指数(上年=100)',\n",
       "       '国有城镇单位就业人员工资总额指数(上年=100)', '其他城镇单位就业人员工资总额指数(上年=100)',\n",
       "       '城镇单位就业人员平均工资', '城镇单位就业人员平均货币工资指数(上年=100)',\n",
       "       '国有城镇单位就业人员平均货币工资指数(上年=100)', '其他城镇单位就业人员平均货币工资指数(上年=100)',\n",
       "       '城镇单位就业人员平均实际工资指数(上年=100)', '国有城镇单位就业人员平均实际工资指数(上年=100)',\n",
       "       '其他城镇单位就业人员平均实际工资指数(上年=100)', '农、林、牧、渔业城镇单位就业人员工资总额',\n",
       "       '采矿业城镇单位就业人员工资总额', '制造业城镇单位就业人员工资总额', '电力、燃气及水的生产和供应业城镇单位就业人员工资总额',\n",
       "       '建筑业城镇单位就业人员工资总额', '交通运输、仓储和邮政业城镇单位就业人员工资总额',\n",
       "       '信息传输、计算机服务和软件业城镇单位就业人员工资总额', '批发和零售业城镇单位就业人员工资总额',\n",
       "       '住宿和餐饮业城镇单位就业人员工资总额', '金融业城镇单位就业人员工资总额', '房地产业城镇单位就业人员工资总额',\n",
       "       '租赁和商务服务业城镇单位就业人员工资总额', '科学研究、技术服务和地质勘查业城镇单位就业人员工资总额',\n",
       "       '水利、环境和公共设施管理业城镇单位就业人员工资总额', '居民服务和其他服务业城镇单位就业人员工资总额',\n",
       "       '教育城镇单位就业人员工资总额', '卫生、社会保障和社会福利业城镇单位就业人员工资总额',\n",
       "       '文化、体育和娱乐业城镇单位就业人员工资总额', '公共管理和社会组织城镇单位就业人员工资总额',\n",
       "       '农、林、牧、渔业城镇单位就业人员平均工资', '采矿业城镇单位就业人员平均工资', '制造业城镇单位就业人员平均工资',\n",
       "       '电力、燃气及水的生产和供应业城镇单位就业人员平均工资', '建筑业城镇单位就业人员平均工资',\n",
       "       '交通运输、仓储和邮政业城镇单位就业人员平均工资', '信息传输、计算机服务和软件业城镇单位就业人员平均工资',\n",
       "       '批发和零售业城镇单位就业人员平均工资', '住宿和餐饮业城镇单位就业人员平均工资', '金融业城镇单位就业人员平均工资',\n",
       "       '房地产业城镇单位就业人员平均工资', '租赁和商务服务业城镇单位就业人员平均工资',\n",
       "       '科学研究、技术服务和地质勘查业城镇单位就业人员平均工资', '水利、环境和公共设施管理业城镇单位就业人员平均工资',\n",
       "       '居民服务和其他服务业城镇单位就业人员平均工资', '教育城镇单位就业人员平均工资',\n",
       "       '卫生、社会保障和社会福利业城镇单位就业人员平均工资', '文化、体育和娱乐业城镇单位就业人员平均工资',\n",
       "       '公共管理和社会组织城镇单位就业人员平均工资'], dtype=object)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看所有指标(   .unique()查看唯一值。   )\n",
    "所有指标 = df_筛选.指标.unique()\n",
    "所有指标"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "hidden": true,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['', '人员'],\n",
       " ['农林牧渔业', '人员'],\n",
       " ['采矿业', '人员'],\n",
       " ['制造业', '人员'],\n",
       " ['电力、燃气及水的生产和供应业', '人员'],\n",
       " ['建筑业', '人员'],\n",
       " ['交通运输、仓储及邮电通信业', '人员'],\n",
       " ['信息传输、计算机服务和软件业', '人员'],\n",
       " ['批发和零售业', '人员'],\n",
       " ['住宿和餐饮业', '人员'],\n",
       " ['金融业', '人员'],\n",
       " ['房地产业', '人员'],\n",
       " ['租赁和商务服务业', '人员'],\n",
       " ['科学研究、技术服务和地质勘查业', '人员'],\n",
       " ['水利、环境和公共设施管理业', '人员'],\n",
       " ['居民服务和其他服务业', '人员'],\n",
       " ['教育业', '人员'],\n",
       " ['卫生、社会保障和社会福利业', '人员'],\n",
       " ['文化、体育和娱乐业', '人员'],\n",
       " ['公共管理和社会组织', '人员'],\n",
       " ['', '人员工资总额'],\n",
       " ['国有', '人员工资总额'],\n",
       " ['其他', '人员工资总额'],\n",
       " ['', '人员工资总额指数(上年=100)'],\n",
       " ['国有', '人员工资总额指数(上年=100)'],\n",
       " ['其他', '人员工资总额指数(上年=100)'],\n",
       " ['', '人员平均工资'],\n",
       " ['', '人员平均货币工资指数(上年=100)'],\n",
       " ['国有', '人员平均货币工资指数(上年=100)'],\n",
       " ['其他', '人员平均货币工资指数(上年=100)'],\n",
       " ['', '人员平均实际工资指数(上年=100)'],\n",
       " ['国有', '人员平均实际工资指数(上年=100)'],\n",
       " ['其他', '人员平均实际工资指数(上年=100)'],\n",
       " ['农、林、牧、渔业', '人员工资总额'],\n",
       " ['采矿业', '人员工资总额'],\n",
       " ['制造业', '人员工资总额'],\n",
       " ['电力、燃气及水的生产和供应业', '人员工资总额'],\n",
       " ['建筑业', '人员工资总额'],\n",
       " ['交通运输、仓储和邮政业', '人员工资总额'],\n",
       " ['信息传输、计算机服务和软件业', '人员工资总额'],\n",
       " ['批发和零售业', '人员工资总额'],\n",
       " ['住宿和餐饮业', '人员工资总额'],\n",
       " ['金融业', '人员工资总额'],\n",
       " ['房地产业', '人员工资总额'],\n",
       " ['租赁和商务服务业', '人员工资总额'],\n",
       " ['科学研究、技术服务和地质勘查业', '人员工资总额'],\n",
       " ['水利、环境和公共设施管理业', '人员工资总额'],\n",
       " ['居民服务和其他服务业', '人员工资总额'],\n",
       " ['教育', '人员工资总额'],\n",
       " ['卫生、社会保障和社会福利业', '人员工资总额'],\n",
       " ['文化、体育和娱乐业', '人员工资总额'],\n",
       " ['公共管理和社会组织', '人员工资总额'],\n",
       " ['农、林、牧、渔业', '人员平均工资'],\n",
       " ['采矿业', '人员平均工资'],\n",
       " ['制造业', '人员平均工资'],\n",
       " ['电力、燃气及水的生产和供应业', '人员平均工资'],\n",
       " ['建筑业', '人员平均工资'],\n",
       " ['交通运输、仓储和邮政业', '人员平均工资'],\n",
       " ['信息传输、计算机服务和软件业', '人员平均工资'],\n",
       " ['批发和零售业', '人员平均工资'],\n",
       " ['住宿和餐饮业', '人员平均工资'],\n",
       " ['金融业', '人员平均工资'],\n",
       " ['房地产业', '人员平均工资'],\n",
       " ['租赁和商务服务业', '人员平均工资'],\n",
       " ['科学研究、技术服务和地质勘查业', '人员平均工资'],\n",
       " ['水利、环境和公共设施管理业', '人员平均工资'],\n",
       " ['居民服务和其他服务业', '人员平均工资'],\n",
       " ['教育', '人员平均工资'],\n",
       " ['卫生、社会保障和社会福利业', '人员平均工资'],\n",
       " ['文化、体育和娱乐业', '人员平均工资'],\n",
       " ['公共管理和社会组织', '人员平均工资']]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将所有指标（字符）根据”城镇单位就业“进行拆分切片\n",
    "\n",
    "# 推导式拆分\n",
    "# 所有指标_拆分=[]\n",
    "# for i in 所有指标:\n",
    "#     暂存=i.split(\"城镇单位就业\")\n",
    "#     所有指标_拆分.append(暂存)\n",
    "# print(所有指标_拆分)\n",
    "\n",
    "所有指标_拆分 = [x.split(\"城镇单位就业\") for x in 所有指标]\n",
    "所有指标_拆分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['农林牧渔业', '人员'],\n",
       " ['采矿业', '人员'],\n",
       " ['制造业', '人员'],\n",
       " ['电力、燃气及水的生产和供应业', '人员'],\n",
       " ['建筑业', '人员'],\n",
       " ['交通运输、仓储及邮电通信业', '人员'],\n",
       " ['信息传输、计算机服务和软件业', '人员'],\n",
       " ['批发和零售业', '人员'],\n",
       " ['住宿和餐饮业', '人员'],\n",
       " ['金融业', '人员'],\n",
       " ['房地产业', '人员'],\n",
       " ['租赁和商务服务业', '人员'],\n",
       " ['科学研究、技术服务和地质勘查业', '人员'],\n",
       " ['水利、环境和公共设施管理业', '人员'],\n",
       " ['居民服务和其他服务业', '人员'],\n",
       " ['教育业', '人员'],\n",
       " ['卫生、社会保障和社会福利业', '人员'],\n",
       " ['文化、体育和娱乐业', '人员'],\n",
       " ['公共管理和社会组织', '人员'],\n",
       " ['农、林、牧、渔业', '人员平均工资'],\n",
       " ['采矿业', '人员平均工资'],\n",
       " ['制造业', '人员平均工资'],\n",
       " ['电力、燃气及水的生产和供应业', '人员平均工资'],\n",
       " ['建筑业', '人员平均工资'],\n",
       " ['交通运输、仓储和邮政业', '人员平均工资'],\n",
       " ['信息传输、计算机服务和软件业', '人员平均工资'],\n",
       " ['批发和零售业', '人员平均工资'],\n",
       " ['住宿和餐饮业', '人员平均工资'],\n",
       " ['金融业', '人员平均工资'],\n",
       " ['房地产业', '人员平均工资'],\n",
       " ['租赁和商务服务业', '人员平均工资'],\n",
       " ['科学研究、技术服务和地质勘查业', '人员平均工资'],\n",
       " ['水利、环境和公共设施管理业', '人员平均工资'],\n",
       " ['居民服务和其他服务业', '人员平均工资'],\n",
       " ['教育', '人员平均工资'],\n",
       " ['卫生、社会保障和社会福利业', '人员平均工资'],\n",
       " ['文化、体育和娱乐业', '人员平均工资'],\n",
       " ['公共管理和社会组织', '人员平均工资']]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 取 “人员” 和“人员平均工资”\n",
    "\n",
    "\n",
    "# 推导式拆分\n",
    "# x暂存=[]\n",
    "# y暂存=[]\n",
    "\n",
    "# for (x,y) in 所有指标_拆分:\n",
    "#     if (y=='人员平均工资' or y=='人员') and x != '':\n",
    "#         x暂存.append(x)\n",
    "#         y暂存.append(y)\n",
    "# 人员和工资指标=[x暂存,y暂存]\n",
    "# 人员和工资指标\n",
    "\n",
    "\n",
    "\n",
    "人员和工资指标 = [[x,y] for (x,y) in 所有指标_拆分 if (y=='人员平均工资' or y=='人员') and x != '']\n",
    "人员和工资指标"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['农林牧渔业城镇单位就业人员',\n",
       " '采矿业城镇单位就业人员',\n",
       " '制造业城镇单位就业人员',\n",
       " '电力、燃气及水的生产和供应业城镇单位就业人员',\n",
       " '建筑业城镇单位就业人员',\n",
       " '交通运输、仓储及邮电通信业城镇单位就业人员',\n",
       " '信息传输、计算机服务和软件业城镇单位就业人员',\n",
       " '批发和零售业城镇单位就业人员',\n",
       " '住宿和餐饮业城镇单位就业人员',\n",
       " '金融业城镇单位就业人员',\n",
       " '房地产业城镇单位就业人员',\n",
       " '租赁和商务服务业城镇单位就业人员',\n",
       " '科学研究、技术服务和地质勘查业城镇单位就业人员',\n",
       " '水利、环境和公共设施管理业城镇单位就业人员',\n",
       " '居民服务和其他服务业城镇单位就业人员',\n",
       " '教育业城镇单位就业人员',\n",
       " '卫生、社会保障和社会福利业城镇单位就业人员',\n",
       " '文化、体育和娱乐业城镇单位就业人员',\n",
       " '公共管理和社会组织城镇单位就业人员',\n",
       " '农、林、牧、渔业城镇单位就业人员平均工资',\n",
       " '采矿业城镇单位就业人员平均工资',\n",
       " '制造业城镇单位就业人员平均工资',\n",
       " '电力、燃气及水的生产和供应业城镇单位就业人员平均工资',\n",
       " '建筑业城镇单位就业人员平均工资',\n",
       " '交通运输、仓储和邮政业城镇单位就业人员平均工资',\n",
       " '信息传输、计算机服务和软件业城镇单位就业人员平均工资',\n",
       " '批发和零售业城镇单位就业人员平均工资',\n",
       " '住宿和餐饮业城镇单位就业人员平均工资',\n",
       " '金融业城镇单位就业人员平均工资',\n",
       " '房地产业城镇单位就业人员平均工资',\n",
       " '租赁和商务服务业城镇单位就业人员平均工资',\n",
       " '科学研究、技术服务和地质勘查业城镇单位就业人员平均工资',\n",
       " '水利、环境和公共设施管理业城镇单位就业人员平均工资',\n",
       " '居民服务和其他服务业城镇单位就业人员平均工资',\n",
       " '教育城镇单位就业人员平均工资',\n",
       " '卫生、社会保障和社会福利业城镇单位就业人员平均工资',\n",
       " '文化、体育和娱乐业城镇单位就业人员平均工资',\n",
       " '公共管理和社会组织城镇单位就业人员平均工资']"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将筛选出来的结果合并拼接\n",
    "\n",
    "人员和工资指标加城镇就业单位 = [\"城镇单位就业\".join(x) for x in 人员和工资指标]\n",
    "人员和工资指标加城镇就业单位"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "# 表格处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指标</th>\n",
       "      <th>地区</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2017</td>\n",
       "      <td>3.4116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2016</td>\n",
       "      <td>3.6867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2015</td>\n",
       "      <td>3.8949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2014</td>\n",
       "      <td>3.2331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11775</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2013</td>\n",
       "      <td>46636.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11776</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2012</td>\n",
       "      <td>45071.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11777</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2011</td>\n",
       "      <td>39862.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11778</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2010</td>\n",
       "      <td>35950.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11779</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2009</td>\n",
       "      <td>31217.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11780 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          指标        地区     年          数据\n",
       "0              农林牧渔业城镇单位就业人员       北京市  2018         NaN\n",
       "1              农林牧渔业城镇单位就业人员       北京市  2017      3.4116\n",
       "2              农林牧渔业城镇单位就业人员       北京市  2016      3.6867\n",
       "3              农林牧渔业城镇单位就业人员       北京市  2015      3.8949\n",
       "4              农林牧渔业城镇单位就业人员       北京市  2014      3.2331\n",
       "...                      ...       ...   ...         ...\n",
       "11775  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2013  46636.0000\n",
       "11776  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2012  45071.0000\n",
       "11777  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2011  39862.0000\n",
       "11778  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2010  35950.0000\n",
       "11779  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2009  31217.0000\n",
       "\n",
       "[11780 rows x 4 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将指标作为索引，选出 【人员和工资指标加城镇就业单位】列表中的含有的数据行。\n",
    "\n",
    "df_处理 = df_筛选.set_index(\"指标\").loc[人员和工资指标加城镇就业单位].reset_index()\n",
    "df_处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指标</th>\n",
       "      <th>地区</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>11780</td>\n",
       "      <td>11780</td>\n",
       "      <td>11780.000000</td>\n",
       "      <td>10591.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>38</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员</td>\n",
       "      <td>上海市</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>310</td>\n",
       "      <td>380</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013.500000</td>\n",
       "      <td>25995.610415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.872403</td>\n",
       "      <td>31837.310363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009.000000</td>\n",
       "      <td>0.024900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011.000000</td>\n",
       "      <td>11.323850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013.500000</td>\n",
       "      <td>11478.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016.000000</td>\n",
       "      <td>46218.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018.000000</td>\n",
       "      <td>253637.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       指标     地区             年             数据\n",
       "count               11780  11780  11780.000000   10591.000000\n",
       "unique                 38     31           NaN            NaN\n",
       "top     公共管理和社会组织城镇单位就业人员    上海市           NaN            NaN\n",
       "freq                  310    380           NaN            NaN\n",
       "mean                  NaN    NaN   2013.500000   25995.610415\n",
       "std                   NaN    NaN      2.872403   31837.310363\n",
       "min                   NaN    NaN   2009.000000       0.024900\n",
       "25%                   NaN    NaN   2011.000000      11.323850\n",
       "50%                   NaN    NaN   2013.500000   11478.000000\n",
       "75%                   NaN    NaN   2016.000000   46218.500000\n",
       "max                   NaN    NaN   2018.000000  253637.000000"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对上述数据进行所有统计的分析\n",
    "df_处理.describe(include=\"all\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>最小值</th>\n",
       "      <th>最大值</th>\n",
       "      <th>平均值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>指标</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>交通运输、仓储及邮电通信业城镇单位就业人员</th>\n",
       "      <td>0.5997</td>\n",
       "      <td>85.3999</td>\n",
       "      <td>24.556320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>交通运输、仓储和邮政业城镇单位就业人员平均工资</th>\n",
       "      <td>25098.0000</td>\n",
       "      <td>116763.0000</td>\n",
       "      <td>56241.240143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>住宿和餐饮业城镇单位就业人员</th>\n",
       "      <td>0.3000</td>\n",
       "      <td>39.3419</td>\n",
       "      <td>8.331891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>住宿和餐饮业城镇单位就业人员平均工资</th>\n",
       "      <td>13455.0000</td>\n",
       "      <td>61095.0000</td>\n",
       "      <td>31198.731183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员</th>\n",
       "      <td>0.2235</td>\n",
       "      <td>77.4400</td>\n",
       "      <td>9.204604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>22186.0000</td>\n",
       "      <td>212063.0000</td>\n",
       "      <td>72507.896057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>公共管理和社会组织城镇单位就业人员</th>\n",
       "      <td>7.8169</td>\n",
       "      <td>116.1762</td>\n",
       "      <td>50.301842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>公共管理和社会组织城镇单位就业人员平均工资</th>\n",
       "      <td>25275.0000</td>\n",
       "      <td>128855.0000</td>\n",
       "      <td>55019.594982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>农、林、牧、渔业城镇单位就业人员平均工资</th>\n",
       "      <td>8832.0000</td>\n",
       "      <td>74975.0000</td>\n",
       "      <td>30661.906810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>农林牧渔业城镇单位就业人员</th>\n",
       "      <td>0.3089</td>\n",
       "      <td>93.9292</td>\n",
       "      <td>10.091744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>制造业城镇单位就业人员</th>\n",
       "      <td>0.6612</td>\n",
       "      <td>1020.2491</td>\n",
       "      <td>145.443355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>制造业城镇单位就业人员平均工资</th>\n",
       "      <td>21508.0000</td>\n",
       "      <td>106835.0000</td>\n",
       "      <td>45198.247312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>卫生、社会保障和社会福利业城镇单位就业人员</th>\n",
       "      <td>1.3830</td>\n",
       "      <td>65.8223</td>\n",
       "      <td>24.421601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>卫生、社会保障和社会福利业城镇单位就业人员平均工资</th>\n",
       "      <td>22302.0000</td>\n",
       "      <td>169191.0000</td>\n",
       "      <td>59073.053763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>居民服务和其他服务业城镇单位就业人员</th>\n",
       "      <td>0.0557</td>\n",
       "      <td>11.0436</td>\n",
       "      <td>2.277055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>居民服务和其他服务业城镇单位就业人员平均工资</th>\n",
       "      <td>16022.0000</td>\n",
       "      <td>67013.0000</td>\n",
       "      <td>35929.476703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇单位就业人员</th>\n",
       "      <td>0.5018</td>\n",
       "      <td>450.1977</td>\n",
       "      <td>72.355329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>16423.0000</td>\n",
       "      <td>99718.0000</td>\n",
       "      <td>40809.896057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>房地产业城镇单位就业人员</th>\n",
       "      <td>0.0249</td>\n",
       "      <td>66.3147</td>\n",
       "      <td>10.849628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>房地产业城镇单位就业人员平均工资</th>\n",
       "      <td>15938.0000</td>\n",
       "      <td>120379.0000</td>\n",
       "      <td>45288.530466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>批发和零售业城镇单位就业人员</th>\n",
       "      <td>0.4833</td>\n",
       "      <td>102.7835</td>\n",
       "      <td>24.356891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>批发和零售业城镇单位就业人员平均工资</th>\n",
       "      <td>16073.0000</td>\n",
       "      <td>139627.0000</td>\n",
       "      <td>44120.035842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>教育业城镇单位就业人员</th>\n",
       "      <td>3.9384</td>\n",
       "      <td>127.4604</td>\n",
       "      <td>53.813387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>教育城镇单位就业人员平均工资</th>\n",
       "      <td>26175.0000</td>\n",
       "      <td>143215.0000</td>\n",
       "      <td>57414.487455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>文化、体育和娱乐业城镇单位就业人员</th>\n",
       "      <td>0.5999</td>\n",
       "      <td>19.0189</td>\n",
       "      <td>4.581411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>文化、体育和娱乐业城镇单位就业人员平均工资</th>\n",
       "      <td>22377.0000</td>\n",
       "      <td>150810.0000</td>\n",
       "      <td>54176.698925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>水利、环境和公共设施管理业城镇单位就业人员</th>\n",
       "      <td>0.1636</td>\n",
       "      <td>18.2523</td>\n",
       "      <td>8.022701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>水利、环境和公共设施管理业城镇单位就业人员平均工资</th>\n",
       "      <td>15831.0000</td>\n",
       "      <td>95341.0000</td>\n",
       "      <td>36995.433692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>电力、燃气及水的生产和供应业城镇单位就业人员</th>\n",
       "      <td>0.7362</td>\n",
       "      <td>32.1821</td>\n",
       "      <td>11.707497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>电力、燃气及水的生产和供应业城镇单位就业人员平均工资</th>\n",
       "      <td>29419.0000</td>\n",
       "      <td>174252.0000</td>\n",
       "      <td>67744.182796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员</th>\n",
       "      <td>0.6790</td>\n",
       "      <td>71.2481</td>\n",
       "      <td>11.614666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>26211.0000</td>\n",
       "      <td>176383.0000</td>\n",
       "      <td>66216.186380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>租赁和商务服务业城镇单位就业人员</th>\n",
       "      <td>0.1000</td>\n",
       "      <td>88.2695</td>\n",
       "      <td>12.672472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>租赁和商务服务业城镇单位就业人员平均工资</th>\n",
       "      <td>16691.0000</td>\n",
       "      <td>156621.0000</td>\n",
       "      <td>43966.784946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>采矿业城镇单位就业人员</th>\n",
       "      <td>0.0523</td>\n",
       "      <td>103.0136</td>\n",
       "      <td>18.220504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>采矿业城镇单位就业人员平均工资</th>\n",
       "      <td>22732.0000</td>\n",
       "      <td>144454.0000</td>\n",
       "      <td>56747.756272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员</th>\n",
       "      <td>0.7717</td>\n",
       "      <td>54.4498</td>\n",
       "      <td>17.982375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>30627.0000</td>\n",
       "      <td>253637.0000</td>\n",
       "      <td>86977.516129</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     数据                           \n",
       "                                    最小值          最大值           平均值\n",
       "指标                                                                \n",
       "交通运输、仓储及邮电通信业城镇单位就业人员            0.5997      85.3999     24.556320\n",
       "交通运输、仓储和邮政业城镇单位就业人员平均工资      25098.0000  116763.0000  56241.240143\n",
       "住宿和餐饮业城镇单位就业人员                   0.3000      39.3419      8.331891\n",
       "住宿和餐饮业城镇单位就业人员平均工资           13455.0000   61095.0000  31198.731183\n",
       "信息传输、计算机服务和软件业城镇单位就业人员           0.2235      77.4400      9.204604\n",
       "信息传输、计算机服务和软件业城镇单位就业人员平均工资   22186.0000  212063.0000  72507.896057\n",
       "公共管理和社会组织城镇单位就业人员                7.8169     116.1762     50.301842\n",
       "公共管理和社会组织城镇单位就业人员平均工资        25275.0000  128855.0000  55019.594982\n",
       "农、林、牧、渔业城镇单位就业人员平均工资          8832.0000   74975.0000  30661.906810\n",
       "农林牧渔业城镇单位就业人员                    0.3089      93.9292     10.091744\n",
       "制造业城镇单位就业人员                      0.6612    1020.2491    145.443355\n",
       "制造业城镇单位就业人员平均工资              21508.0000  106835.0000  45198.247312\n",
       "卫生、社会保障和社会福利业城镇单位就业人员            1.3830      65.8223     24.421601\n",
       "卫生、社会保障和社会福利业城镇单位就业人员平均工资    22302.0000  169191.0000  59073.053763\n",
       "居民服务和其他服务业城镇单位就业人员               0.0557      11.0436      2.277055\n",
       "居民服务和其他服务业城镇单位就业人员平均工资       16022.0000   67013.0000  35929.476703\n",
       "建筑业城镇单位就业人员                      0.5018     450.1977     72.355329\n",
       "建筑业城镇单位就业人员平均工资              16423.0000   99718.0000  40809.896057\n",
       "房地产业城镇单位就业人员                     0.0249      66.3147     10.849628\n",
       "房地产业城镇单位就业人员平均工资             15938.0000  120379.0000  45288.530466\n",
       "批发和零售业城镇单位就业人员                   0.4833     102.7835     24.356891\n",
       "批发和零售业城镇单位就业人员平均工资           16073.0000  139627.0000  44120.035842\n",
       "教育业城镇单位就业人员                      3.9384     127.4604     53.813387\n",
       "教育城镇单位就业人员平均工资               26175.0000  143215.0000  57414.487455\n",
       "文化、体育和娱乐业城镇单位就业人员                0.5999      19.0189      4.581411\n",
       "文化、体育和娱乐业城镇单位就业人员平均工资        22377.0000  150810.0000  54176.698925\n",
       "水利、环境和公共设施管理业城镇单位就业人员            0.1636      18.2523      8.022701\n",
       "水利、环境和公共设施管理业城镇单位就业人员平均工资    15831.0000   95341.0000  36995.433692\n",
       "电力、燃气及水的生产和供应业城镇单位就业人员           0.7362      32.1821     11.707497\n",
       "电力、燃气及水的生产和供应业城镇单位就业人员平均工资   29419.0000  174252.0000  67744.182796\n",
       "科学研究、技术服务和地质勘查业城镇单位就业人员          0.6790      71.2481     11.614666\n",
       "科学研究、技术服务和地质勘查业城镇单位就业人员平均工资  26211.0000  176383.0000  66216.186380\n",
       "租赁和商务服务业城镇单位就业人员                 0.1000      88.2695     12.672472\n",
       "租赁和商务服务业城镇单位就业人员平均工资         16691.0000  156621.0000  43966.784946\n",
       "采矿业城镇单位就业人员                      0.0523     103.0136     18.220504\n",
       "采矿业城镇单位就业人员平均工资              22732.0000  144454.0000  56747.756272\n",
       "金融业城镇单位就业人员                      0.7717      54.4498     17.982375\n",
       "金融业城镇单位就业人员平均工资              30627.0000  253637.0000  86977.516129"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 以“指标”分组，再加上“数据”进行查看\n",
    "df_处理.groupby(by=\"指标\").agg({\"数据\":[\"min\",\"max\",\"mean\"]}).rename(columns={'min':'最小值','max':'最大值','mean':'平均值'}) #.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指标</th>\n",
       "      <th>地区</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "      <th>行业</th>\n",
       "      <th>行业指标</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2017</td>\n",
       "      <td>3.4116</td>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2016</td>\n",
       "      <td>3.6867</td>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2015</td>\n",
       "      <td>3.8949</td>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2014</td>\n",
       "      <td>3.2331</td>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11775</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2013</td>\n",
       "      <td>46636.0000</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11776</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2012</td>\n",
       "      <td>45071.0000</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11777</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2011</td>\n",
       "      <td>39862.0000</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11778</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2010</td>\n",
       "      <td>35950.0000</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11779</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2009</td>\n",
       "      <td>31217.0000</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11780 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          指标        地区     年          数据         行业    行业指标\n",
       "0              农林牧渔业城镇单位就业人员       北京市  2018         NaN      农林牧渔业      人员\n",
       "1              农林牧渔业城镇单位就业人员       北京市  2017      3.4116      农林牧渔业      人员\n",
       "2              农林牧渔业城镇单位就业人员       北京市  2016      3.6867      农林牧渔业      人员\n",
       "3              农林牧渔业城镇单位就业人员       北京市  2015      3.8949      农林牧渔业      人员\n",
       "4              农林牧渔业城镇单位就业人员       北京市  2014      3.2331      农林牧渔业      人员\n",
       "...                      ...       ...   ...         ...        ...     ...\n",
       "11775  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2013  46636.0000  公共管理和社会组织  人员平均工资\n",
       "11776  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2012  45071.0000  公共管理和社会组织  人员平均工资\n",
       "11777  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2011  39862.0000  公共管理和社会组织  人员平均工资\n",
       "11778  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2010  35950.0000  公共管理和社会组织  人员平均工资\n",
       "11779  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2009  31217.0000  公共管理和社会组织  人员平均工资\n",
       "\n",
       "[11780 rows x 6 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 多层次数据分析\n",
    "# 以“指标”中的“城镇就业单位”进行切片操作，分别去前后的值（各行业&指标），并增加列【行业】&【行业指标】\n",
    "\n",
    "df_处理[\"行业\"] = [x.split(\"城镇单位就业\")[0] for x in df_处理.指标] # \n",
    "df_处理[\"行业指标\"] = [x.split(\"城镇单位就业\")[1] for x in df_处理.指标]\n",
    "df_处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>最小值</th>\n",
       "      <th>最大值</th>\n",
       "      <th>平均值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th>行业</th>\n",
       "      <th>行业指标</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">上海市</th>\n",
       "      <th>交通运输、仓储及邮电通信业</th>\n",
       "      <th>人员</th>\n",
       "      <td>35.6329</td>\n",
       "      <td>51.4541</td>\n",
       "      <td>45.014167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>交通运输、仓储和邮政业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>49847.0000</td>\n",
       "      <td>116763.0000</td>\n",
       "      <td>81817.888889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">住宿和餐饮业</th>\n",
       "      <th>人员</th>\n",
       "      <td>10.8191</td>\n",
       "      <td>25.5494</td>\n",
       "      <td>19.851000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>29564.0000</td>\n",
       "      <td>60153.0000</td>\n",
       "      <td>45158.777778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业</th>\n",
       "      <th>人员</th>\n",
       "      <td>6.5150</td>\n",
       "      <td>30.7312</td>\n",
       "      <td>17.706767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">黑龙江省</th>\n",
       "      <th>租赁和商务服务业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>21157.0000</td>\n",
       "      <td>56493.0000</td>\n",
       "      <td>38601.444444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">采矿业</th>\n",
       "      <th>人员</th>\n",
       "      <td>25.5592</td>\n",
       "      <td>43.4887</td>\n",
       "      <td>36.610556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>33417.0000</td>\n",
       "      <td>68926.0000</td>\n",
       "      <td>51564.777778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">金融业</th>\n",
       "      <th>人员</th>\n",
       "      <td>13.4821</td>\n",
       "      <td>22.6548</td>\n",
       "      <td>17.099533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>37056.0000</td>\n",
       "      <td>66790.0000</td>\n",
       "      <td>55194.888889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1178 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    数据                           \n",
       "                                   最小值          最大值           平均值\n",
       "地区   行业             行业指标                                         \n",
       "上海市  交通运输、仓储及邮电通信业  人员         35.6329      51.4541     45.014167\n",
       "     交通运输、仓储和邮政业    人员平均工资  49847.0000  116763.0000  81817.888889\n",
       "     住宿和餐饮业         人员         10.8191      25.5494     19.851000\n",
       "                    人员平均工资  29564.0000   60153.0000  45158.777778\n",
       "     信息传输、计算机服务和软件业 人员          6.5150      30.7312     17.706767\n",
       "...                                ...          ...           ...\n",
       "黑龙江省 租赁和商务服务业       人员平均工资  21157.0000   56493.0000  38601.444444\n",
       "     采矿业            人员         25.5592      43.4887     36.610556\n",
       "                    人员平均工资  33417.0000   68926.0000  51564.777778\n",
       "     金融业            人员         13.4821      22.6548     17.099533\n",
       "                    人员平均工资  37056.0000   66790.0000  55194.888889\n",
       "\n",
       "[1178 rows x 3 columns]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 分别以\"地区\",\"行业\",\"行业指标\"来分组。\n",
    "\n",
    "df_处理.groupby([\"地区\",\"行业\",\"行业指标\"]).agg({\"数据\":[\"min\",\"max\",\"mean\"]}).rename(columns={'min':'最小值','max':'最大值','mean':'平均值'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指标</th>\n",
       "      <th>地区</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "      <th>行业</th>\n",
       "      <th>行业指标</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5890</th>\n",
       "      <td>农、林、牧、渔业城镇单位就业人员平均工资</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农、林、牧、渔业</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5891</th>\n",
       "      <td>农、林、牧、渔业城镇单位就业人员平均工资</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2017</td>\n",
       "      <td>55218.0</td>\n",
       "      <td>农、林、牧、渔业</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5892</th>\n",
       "      <td>农、林、牧、渔业城镇单位就业人员平均工资</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2016</td>\n",
       "      <td>51941.0</td>\n",
       "      <td>农、林、牧、渔业</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5893</th>\n",
       "      <td>农、林、牧、渔业城镇单位就业人员平均工资</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2015</td>\n",
       "      <td>50797.0</td>\n",
       "      <td>农、林、牧、渔业</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5894</th>\n",
       "      <td>农、林、牧、渔业城镇单位就业人员平均工资</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2014</td>\n",
       "      <td>49478.0</td>\n",
       "      <td>农、林、牧、渔业</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11775</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2013</td>\n",
       "      <td>46636.0</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11776</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2012</td>\n",
       "      <td>45071.0</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11777</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2011</td>\n",
       "      <td>39862.0</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11778</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2010</td>\n",
       "      <td>35950.0</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11779</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2009</td>\n",
       "      <td>31217.0</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5890 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          指标        地区     年       数据         行业    行业指标\n",
       "5890    农、林、牧、渔业城镇单位就业人员平均工资       北京市  2018      NaN   农、林、牧、渔业  人员平均工资\n",
       "5891    农、林、牧、渔业城镇单位就业人员平均工资       北京市  2017  55218.0   农、林、牧、渔业  人员平均工资\n",
       "5892    农、林、牧、渔业城镇单位就业人员平均工资       北京市  2016  51941.0   农、林、牧、渔业  人员平均工资\n",
       "5893    农、林、牧、渔业城镇单位就业人员平均工资       北京市  2015  50797.0   农、林、牧、渔业  人员平均工资\n",
       "5894    农、林、牧、渔业城镇单位就业人员平均工资       北京市  2014  49478.0   农、林、牧、渔业  人员平均工资\n",
       "...                      ...       ...   ...      ...        ...     ...\n",
       "11775  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2013  46636.0  公共管理和社会组织  人员平均工资\n",
       "11776  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2012  45071.0  公共管理和社会组织  人员平均工资\n",
       "11777  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2011  39862.0  公共管理和社会组织  人员平均工资\n",
       "11778  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2010  35950.0  公共管理和社会组织  人员平均工资\n",
       "11779  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2009  31217.0  公共管理和社会组织  人员平均工资\n",
       "\n",
       "[5890 rows x 6 columns]"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df.query()函数能够对数据框进行（挑选行）的操作（https://www.jianshu.com/p/d174c6ed6d2e）\n",
    "# 挑选“行业指标”中“人员平均工资”\n",
    "\n",
    "人员平均工资 = df_处理.query(\"行业指标=='人员平均工资'\")\n",
    "人员平均工资"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>最小值</th>\n",
       "      <th>最大值</th>\n",
       "      <th>平均值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>行业</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">农、林、牧、渔业</th>\n",
       "      <th>辽宁省</th>\n",
       "      <td>8832.0</td>\n",
       "      <td>17027.0</td>\n",
       "      <td>12813.111111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北省</th>\n",
       "      <td>11330.0</td>\n",
       "      <td>23327.0</td>\n",
       "      <td>16067.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江省</th>\n",
       "      <td>11079.0</td>\n",
       "      <td>30638.0</td>\n",
       "      <td>21949.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏自治区</th>\n",
       "      <td>13522.0</td>\n",
       "      <td>41370.0</td>\n",
       "      <td>22214.888889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>水利、环境和公共设施管理业</th>\n",
       "      <th>山西省</th>\n",
       "      <td>15831.0</td>\n",
       "      <td>30961.0</td>\n",
       "      <td>23166.111111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业</th>\n",
       "      <th>西藏自治区</th>\n",
       "      <td>76650.0</td>\n",
       "      <td>186085.0</td>\n",
       "      <td>133585.222222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">信息传输、计算机服务和软件业</th>\n",
       "      <th>北京市</th>\n",
       "      <td>100794.0</td>\n",
       "      <td>183183.0</td>\n",
       "      <td>139098.888889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>上海市</th>\n",
       "      <td>101367.0</td>\n",
       "      <td>212063.0</td>\n",
       "      <td>153913.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">金融业</th>\n",
       "      <th>上海市</th>\n",
       "      <td>134581.0</td>\n",
       "      <td>247568.0</td>\n",
       "      <td>188385.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京市</th>\n",
       "      <td>143187.0</td>\n",
       "      <td>253637.0</td>\n",
       "      <td>204188.555556</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>589 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            数据                         \n",
       "                           最小值       最大值            平均值\n",
       "行业             地区                                      \n",
       "农、林、牧、渔业       辽宁省      8832.0   17027.0   12813.111111\n",
       "               河北省     11330.0   23327.0   16067.333333\n",
       "               黑龙江省    11079.0   30638.0   21949.555556\n",
       "               西藏自治区   13522.0   41370.0   22214.888889\n",
       "水利、环境和公共设施管理业  山西省     15831.0   30961.0   23166.111111\n",
       "...                        ...       ...            ...\n",
       "金融业            西藏自治区   76650.0  186085.0  133585.222222\n",
       "信息传输、计算机服务和软件业 北京市    100794.0  183183.0  139098.888889\n",
       "               上海市    101367.0  212063.0  153913.666667\n",
       "金融业            上海市    134581.0  247568.0  188385.000000\n",
       "               北京市    143187.0  253637.0  204188.555556\n",
       "\n",
       "[589 rows x 3 columns]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 分组（以行业以及地区）\n",
    "\n",
    "人员平均工资.groupby([\"行业\",\"地区\"]).agg({\"数据\":[\"min\",\"max\",\"mean\"]}).sort_values(by=(\"数据\",\"mean\")).rename(columns={'min':'最小值','max':'最大值','mean':'平均值'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>最小值</th>\n",
       "      <th>最大值</th>\n",
       "      <th>平均值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海市</th>\n",
       "      <td>29564.0</td>\n",
       "      <td>247568.0</td>\n",
       "      <td>95373.456140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京市</th>\n",
       "      <td>25006.0</td>\n",
       "      <td>253637.0</td>\n",
       "      <td>89086.046784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津市</th>\n",
       "      <td>20992.0</td>\n",
       "      <td>151778.0</td>\n",
       "      <td>73773.017544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江省</th>\n",
       "      <td>22456.0</td>\n",
       "      <td>165532.0</td>\n",
       "      <td>66680.111111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东省</th>\n",
       "      <td>14469.0</td>\n",
       "      <td>149936.0</td>\n",
       "      <td>63142.736842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏省</th>\n",
       "      <td>17904.0</td>\n",
       "      <td>143002.0</td>\n",
       "      <td>61104.783626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏自治区</th>\n",
       "      <td>13522.0</td>\n",
       "      <td>186085.0</td>\n",
       "      <td>60094.643275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建省</th>\n",
       "      <td>15790.0</td>\n",
       "      <td>109757.0</td>\n",
       "      <td>52346.286550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆市</th>\n",
       "      <td>17129.0</td>\n",
       "      <td>126739.0</td>\n",
       "      <td>52280.444444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东省</th>\n",
       "      <td>19568.0</td>\n",
       "      <td>94704.0</td>\n",
       "      <td>50366.672515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆维吾尔自治区</th>\n",
       "      <td>16022.0</td>\n",
       "      <td>105881.0</td>\n",
       "      <td>50234.625731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川省</th>\n",
       "      <td>17748.0</td>\n",
       "      <td>101514.0</td>\n",
       "      <td>49618.280702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海省</th>\n",
       "      <td>16825.0</td>\n",
       "      <td>100823.0</td>\n",
       "      <td>49264.678363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏回族自治区</th>\n",
       "      <td>15529.0</td>\n",
       "      <td>103768.0</td>\n",
       "      <td>49129.701754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古自治区</th>\n",
       "      <td>15017.0</td>\n",
       "      <td>85135.0</td>\n",
       "      <td>47498.690058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州省</th>\n",
       "      <td>16895.0</td>\n",
       "      <td>141959.0</td>\n",
       "      <td>47309.122807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南省</th>\n",
       "      <td>10855.0</td>\n",
       "      <td>124017.0</td>\n",
       "      <td>47224.847953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西省</th>\n",
       "      <td>16338.0</td>\n",
       "      <td>130891.0</td>\n",
       "      <td>46612.181287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽省</th>\n",
       "      <td>13437.0</td>\n",
       "      <td>98235.0</td>\n",
       "      <td>45770.783626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖北省</th>\n",
       "      <td>15777.0</td>\n",
       "      <td>101551.0</td>\n",
       "      <td>45400.081871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁省</th>\n",
       "      <td>8832.0</td>\n",
       "      <td>91990.0</td>\n",
       "      <td>45122.994152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南省</th>\n",
       "      <td>14853.0</td>\n",
       "      <td>130774.0</td>\n",
       "      <td>44699.356725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖南省</th>\n",
       "      <td>14519.0</td>\n",
       "      <td>99320.0</td>\n",
       "      <td>43279.467836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西壮族自治区</th>\n",
       "      <td>15397.0</td>\n",
       "      <td>96818.0</td>\n",
       "      <td>43223.590643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北省</th>\n",
       "      <td>11330.0</td>\n",
       "      <td>109196.0</td>\n",
       "      <td>42770.233918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江省</th>\n",
       "      <td>11079.0</td>\n",
       "      <td>73978.0</td>\n",
       "      <td>42505.538012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西省</th>\n",
       "      <td>15049.0</td>\n",
       "      <td>84304.0</td>\n",
       "      <td>42360.959064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃省</th>\n",
       "      <td>15163.0</td>\n",
       "      <td>85197.0</td>\n",
       "      <td>40974.327485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南省</th>\n",
       "      <td>15718.0</td>\n",
       "      <td>103314.0</td>\n",
       "      <td>40797.461988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林省</th>\n",
       "      <td>13636.0</td>\n",
       "      <td>87154.0</td>\n",
       "      <td>40642.450292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西省</th>\n",
       "      <td>13455.0</td>\n",
       "      <td>80556.0</td>\n",
       "      <td>40518.602339</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               数据                        \n",
       "              最小值       最大值           平均值\n",
       "地区                                       \n",
       "上海市       29564.0  247568.0  95373.456140\n",
       "北京市       25006.0  253637.0  89086.046784\n",
       "天津市       20992.0  151778.0  73773.017544\n",
       "浙江省       22456.0  165532.0  66680.111111\n",
       "广东省       14469.0  149936.0  63142.736842\n",
       "江苏省       17904.0  143002.0  61104.783626\n",
       "西藏自治区     13522.0  186085.0  60094.643275\n",
       "福建省       15790.0  109757.0  52346.286550\n",
       "重庆市       17129.0  126739.0  52280.444444\n",
       "山东省       19568.0   94704.0  50366.672515\n",
       "新疆维吾尔自治区  16022.0  105881.0  50234.625731\n",
       "四川省       17748.0  101514.0  49618.280702\n",
       "青海省       16825.0  100823.0  49264.678363\n",
       "宁夏回族自治区   15529.0  103768.0  49129.701754\n",
       "内蒙古自治区    15017.0   85135.0  47498.690058\n",
       "贵州省       16895.0  141959.0  47309.122807\n",
       "海南省       10855.0  124017.0  47224.847953\n",
       "陕西省       16338.0  130891.0  46612.181287\n",
       "安徽省       13437.0   98235.0  45770.783626\n",
       "湖北省       15777.0  101551.0  45400.081871\n",
       "辽宁省        8832.0   91990.0  45122.994152\n",
       "云南省       14853.0  130774.0  44699.356725\n",
       "湖南省       14519.0   99320.0  43279.467836\n",
       "广西壮族自治区   15397.0   96818.0  43223.590643\n",
       "河北省       11330.0  109196.0  42770.233918\n",
       "黑龙江省      11079.0   73978.0  42505.538012\n",
       "江西省       15049.0   84304.0  42360.959064\n",
       "甘肃省       15163.0   85197.0  40974.327485\n",
       "河南省       15718.0  103314.0  40797.461988\n",
       "吉林省       13636.0   87154.0  40642.450292\n",
       "山西省       13455.0   80556.0  40518.602339"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 分组（以地区）\n",
    "\n",
    "地区分组=人员平均工资.groupby([\"地区\"]).agg({\"数据\":[\"min\",\"max\",\"mean\"]}).sort_values(by=(\"数据\",\"mean\"),ascending=False).rename(columns={'min':'最小值','max':'最大值','mean':'平均值'})\n",
    "地区分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>最小值</th>\n",
       "      <th>最大值</th>\n",
       "      <th>平均值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>行业</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>交通运输、仓储和邮政业</th>\n",
       "      <td>25098.0</td>\n",
       "      <td>116763.0</td>\n",
       "      <td>56241.240143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>住宿和餐饮业</th>\n",
       "      <td>13455.0</td>\n",
       "      <td>61095.0</td>\n",
       "      <td>31198.731183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业</th>\n",
       "      <td>22186.0</td>\n",
       "      <td>212063.0</td>\n",
       "      <td>72507.896057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>公共管理和社会组织</th>\n",
       "      <td>25275.0</td>\n",
       "      <td>128855.0</td>\n",
       "      <td>55019.594982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>农、林、牧、渔业</th>\n",
       "      <td>8832.0</td>\n",
       "      <td>74975.0</td>\n",
       "      <td>30661.906810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>制造业</th>\n",
       "      <td>21508.0</td>\n",
       "      <td>106835.0</td>\n",
       "      <td>45198.247312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>卫生、社会保障和社会福利业</th>\n",
       "      <td>22302.0</td>\n",
       "      <td>169191.0</td>\n",
       "      <td>59073.053763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>居民服务和其他服务业</th>\n",
       "      <td>16022.0</td>\n",
       "      <td>67013.0</td>\n",
       "      <td>35929.476703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业</th>\n",
       "      <td>16423.0</td>\n",
       "      <td>99718.0</td>\n",
       "      <td>40809.896057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>房地产业</th>\n",
       "      <td>15938.0</td>\n",
       "      <td>120379.0</td>\n",
       "      <td>45288.530466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>批发和零售业</th>\n",
       "      <td>16073.0</td>\n",
       "      <td>139627.0</td>\n",
       "      <td>44120.035842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>教育</th>\n",
       "      <td>26175.0</td>\n",
       "      <td>143215.0</td>\n",
       "      <td>57414.487455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>文化、体育和娱乐业</th>\n",
       "      <td>22377.0</td>\n",
       "      <td>150810.0</td>\n",
       "      <td>54176.698925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>水利、环境和公共设施管理业</th>\n",
       "      <td>15831.0</td>\n",
       "      <td>95341.0</td>\n",
       "      <td>36995.433692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>电力、燃气及水的生产和供应业</th>\n",
       "      <td>29419.0</td>\n",
       "      <td>174252.0</td>\n",
       "      <td>67744.182796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业</th>\n",
       "      <td>26211.0</td>\n",
       "      <td>176383.0</td>\n",
       "      <td>66216.186380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>租赁和商务服务业</th>\n",
       "      <td>16691.0</td>\n",
       "      <td>156621.0</td>\n",
       "      <td>43966.784946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>采矿业</th>\n",
       "      <td>22732.0</td>\n",
       "      <td>144454.0</td>\n",
       "      <td>56747.756272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业</th>\n",
       "      <td>30627.0</td>\n",
       "      <td>253637.0</td>\n",
       "      <td>86977.516129</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      数据                        \n",
       "                     最小值       最大值           平均值\n",
       "行业                                              \n",
       "交通运输、仓储和邮政业      25098.0  116763.0  56241.240143\n",
       "住宿和餐饮业           13455.0   61095.0  31198.731183\n",
       "信息传输、计算机服务和软件业   22186.0  212063.0  72507.896057\n",
       "公共管理和社会组织        25275.0  128855.0  55019.594982\n",
       "农、林、牧、渔业          8832.0   74975.0  30661.906810\n",
       "制造业              21508.0  106835.0  45198.247312\n",
       "卫生、社会保障和社会福利业    22302.0  169191.0  59073.053763\n",
       "居民服务和其他服务业       16022.0   67013.0  35929.476703\n",
       "建筑业              16423.0   99718.0  40809.896057\n",
       "房地产业             15938.0  120379.0  45288.530466\n",
       "批发和零售业           16073.0  139627.0  44120.035842\n",
       "教育               26175.0  143215.0  57414.487455\n",
       "文化、体育和娱乐业        22377.0  150810.0  54176.698925\n",
       "水利、环境和公共设施管理业    15831.0   95341.0  36995.433692\n",
       "电力、燃气及水的生产和供应业   29419.0  174252.0  67744.182796\n",
       "科学研究、技术服务和地质勘查业  26211.0  176383.0  66216.186380\n",
       "租赁和商务服务业         16691.0  156621.0  43966.784946\n",
       "采矿业              22732.0  144454.0  56747.756272\n",
       "金融业              30627.0  253637.0  86977.516129"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "行业分组 = 人员平均工资.groupby([\"行业\"]).agg({\"数据\":[\"min\",\"max\",\"mean\"]}).rename(columns={'min':'最小值','max':'最大值','mean':'平均值'})\n",
    "行业分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>最小值</th>\n",
       "      <th>最大值</th>\n",
       "      <th>平均值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海市</th>\n",
       "      <td>29564.0</td>\n",
       "      <td>247568.0</td>\n",
       "      <td>95373.456140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京市</th>\n",
       "      <td>25006.0</td>\n",
       "      <td>253637.0</td>\n",
       "      <td>89086.046784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津市</th>\n",
       "      <td>20992.0</td>\n",
       "      <td>151778.0</td>\n",
       "      <td>73773.017544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江省</th>\n",
       "      <td>22456.0</td>\n",
       "      <td>165532.0</td>\n",
       "      <td>66680.111111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东省</th>\n",
       "      <td>14469.0</td>\n",
       "      <td>149936.0</td>\n",
       "      <td>63142.736842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏省</th>\n",
       "      <td>17904.0</td>\n",
       "      <td>143002.0</td>\n",
       "      <td>61104.783626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏自治区</th>\n",
       "      <td>13522.0</td>\n",
       "      <td>186085.0</td>\n",
       "      <td>60094.643275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建省</th>\n",
       "      <td>15790.0</td>\n",
       "      <td>109757.0</td>\n",
       "      <td>52346.286550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆市</th>\n",
       "      <td>17129.0</td>\n",
       "      <td>126739.0</td>\n",
       "      <td>52280.444444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东省</th>\n",
       "      <td>19568.0</td>\n",
       "      <td>94704.0</td>\n",
       "      <td>50366.672515</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            数据                        \n",
       "           最小值       最大值           平均值\n",
       "地区                                    \n",
       "上海市    29564.0  247568.0  95373.456140\n",
       "北京市    25006.0  253637.0  89086.046784\n",
       "天津市    20992.0  151778.0  73773.017544\n",
       "浙江省    22456.0  165532.0  66680.111111\n",
       "广东省    14469.0  149936.0  63142.736842\n",
       "江苏省    17904.0  143002.0  61104.783626\n",
       "西藏自治区  13522.0  186085.0  60094.643275\n",
       "福建省    15790.0  109757.0  52346.286550\n",
       "重庆市    17129.0  126739.0  52280.444444\n",
       "山东省    19568.0   94704.0  50366.672515"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将地区分组表以“数据”中的“平均值”进行排序\n",
    "\n",
    "地区分组.sort_values(by=(\"数据\",\"平均值\"),ascending=False).rename(columns={'min':'最小值','max':'最大值','mean':'平均值'}).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>最小值</th>\n",
       "      <th>最大值</th>\n",
       "      <th>平均值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>行业</th>\n",
       "      <th>行业指标</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">交通运输、仓储及邮电通信业</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">人员</th>\n",
       "      <th>上海市</th>\n",
       "      <td>35.6329</td>\n",
       "      <td>51.4541</td>\n",
       "      <td>45.014167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南省</th>\n",
       "      <td>13.4000</td>\n",
       "      <td>17.8598</td>\n",
       "      <td>15.667100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古自治区</th>\n",
       "      <td>16.2429</td>\n",
       "      <td>22.7777</td>\n",
       "      <td>19.266189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京市</th>\n",
       "      <td>50.0662</td>\n",
       "      <td>60.2262</td>\n",
       "      <td>56.857056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林省</th>\n",
       "      <td>14.2939</td>\n",
       "      <td>17.1587</td>\n",
       "      <td>15.836233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">金融业</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">人员平均工资</th>\n",
       "      <th>辽宁省</th>\n",
       "      <td>45038.0000</td>\n",
       "      <td>85433.0000</td>\n",
       "      <td>70013.222222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆市</th>\n",
       "      <td>49871.0000</td>\n",
       "      <td>126739.0000</td>\n",
       "      <td>95005.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西省</th>\n",
       "      <td>46122.0000</td>\n",
       "      <td>86024.0000</td>\n",
       "      <td>66379.888889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海省</th>\n",
       "      <td>37456.0000</td>\n",
       "      <td>98911.0000</td>\n",
       "      <td>67431.111111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江省</th>\n",
       "      <td>37056.0000</td>\n",
       "      <td>66790.0000</td>\n",
       "      <td>55194.888889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1178 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     数据                           \n",
       "                                    最小值          最大值           平均值\n",
       "行业            行业指标   地区                                           \n",
       "交通运输、仓储及邮电通信业 人员     上海市        35.6329      51.4541     45.014167\n",
       "                     云南省        13.4000      17.8598     15.667100\n",
       "                     内蒙古自治区     16.2429      22.7777     19.266189\n",
       "                     北京市        50.0662      60.2262     56.857056\n",
       "                     吉林省        14.2939      17.1587     15.836233\n",
       "...                                 ...          ...           ...\n",
       "金融业           人员平均工资 辽宁省     45038.0000   85433.0000  70013.222222\n",
       "                     重庆市     49871.0000  126739.0000  95005.666667\n",
       "                     陕西省     46122.0000   86024.0000  66379.888889\n",
       "                     青海省     37456.0000   98911.0000  67431.111111\n",
       "                     黑龙江省    37056.0000   66790.0000  55194.888889\n",
       "\n",
       "[1178 rows x 3 columns]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 用\"行业\",\"行业指标\",\"地区\"来分组\n",
    "\n",
    "df_处理.groupby([\"行业\",\"行业指标\",\"地区\"]).agg({\"数据\":[\"min\",\"max\",\"mean\"]}).rename(columns={'min':'最小值','max':'最大值','mean':'平均值'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "# 字符替换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>行业</th>\n",
       "      <th>指标</th>\n",
       "      <th>地区</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "      <th>行业指标</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2017</td>\n",
       "      <td>3.4116</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2016</td>\n",
       "      <td>3.6867</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2015</td>\n",
       "      <td>3.8949</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2014</td>\n",
       "      <td>3.2331</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11775</th>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2013</td>\n",
       "      <td>46636.0000</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11776</th>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2012</td>\n",
       "      <td>45071.0000</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11777</th>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2011</td>\n",
       "      <td>39862.0000</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11778</th>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2010</td>\n",
       "      <td>35950.0000</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11779</th>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2009</td>\n",
       "      <td>31217.0000</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11780 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              行业                     指标        地区     年          数据    行业指标\n",
       "0          农林牧渔业          农林牧渔业城镇单位就业人员       北京市  2018         NaN      人员\n",
       "1          农林牧渔业          农林牧渔业城镇单位就业人员       北京市  2017      3.4116      人员\n",
       "2          农林牧渔业          农林牧渔业城镇单位就业人员       北京市  2016      3.6867      人员\n",
       "3          农林牧渔业          农林牧渔业城镇单位就业人员       北京市  2015      3.8949      人员\n",
       "4          农林牧渔业          农林牧渔业城镇单位就业人员       北京市  2014      3.2331      人员\n",
       "...          ...                    ...       ...   ...         ...     ...\n",
       "11775  公共管理和社会组织  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2013  46636.0000  人员平均工资\n",
       "11776  公共管理和社会组织  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2012  45071.0000  人员平均工资\n",
       "11777  公共管理和社会组织  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2011  39862.0000  人员平均工资\n",
       "11778  公共管理和社会组织  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2010  35950.0000  人员平均工资\n",
       "11779  公共管理和社会组织  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2009  31217.0000  人员平均工资\n",
       "\n",
       "[11780 rows x 6 columns]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将字符简化统一\n",
    "\n",
    "统一字词 = {\"交通运输、仓储及邮电通信业\":\"交通运输、仓储和邮政业\",\\\n",
    "            \"交通运输、仓储和邮政业\":\"交通运输、仓储和邮政业\",\\\n",
    "            \"农、林、牧、渔业\":\"农林牧渔业\",\\\n",
    "            \"教育\":\"教育业\",\"教育业业\":\"教育业\"}\n",
    "df_处理2 = df_处理.set_index(\"行业\").rename(index=统一字词).reset_index()\n",
    "# df_处理2.行业.unique()\n",
    "df_处理2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>最小值</th>\n",
       "      <th>最大值</th>\n",
       "      <th>平均值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>行业</th>\n",
       "      <th>行业指标</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">交通运输、仓储和邮政业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.5997</td>\n",
       "      <td>85.3999</td>\n",
       "      <td>24.556320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>25098.0000</td>\n",
       "      <td>116763.0000</td>\n",
       "      <td>56241.240143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">住宿和餐饮业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.3000</td>\n",
       "      <td>39.3419</td>\n",
       "      <td>8.331891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>13455.0000</td>\n",
       "      <td>61095.0000</td>\n",
       "      <td>31198.731183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">信息传输、计算机服务和软件业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.2235</td>\n",
       "      <td>77.4400</td>\n",
       "      <td>9.204604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>22186.0000</td>\n",
       "      <td>212063.0000</td>\n",
       "      <td>72507.896057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">公共管理和社会组织</th>\n",
       "      <th>人员</th>\n",
       "      <td>7.8169</td>\n",
       "      <td>116.1762</td>\n",
       "      <td>50.301842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>25275.0000</td>\n",
       "      <td>128855.0000</td>\n",
       "      <td>55019.594982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">农林牧渔业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.3089</td>\n",
       "      <td>93.9292</td>\n",
       "      <td>10.091744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>8832.0000</td>\n",
       "      <td>74975.0000</td>\n",
       "      <td>30661.906810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">制造业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.6612</td>\n",
       "      <td>1020.2491</td>\n",
       "      <td>145.443355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>21508.0000</td>\n",
       "      <td>106835.0000</td>\n",
       "      <td>45198.247312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">卫生、社会保障和社会福利业</th>\n",
       "      <th>人员</th>\n",
       "      <td>1.3830</td>\n",
       "      <td>65.8223</td>\n",
       "      <td>24.421601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>22302.0000</td>\n",
       "      <td>169191.0000</td>\n",
       "      <td>59073.053763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">居民服务和其他服务业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.0557</td>\n",
       "      <td>11.0436</td>\n",
       "      <td>2.277055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>16022.0000</td>\n",
       "      <td>67013.0000</td>\n",
       "      <td>35929.476703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">建筑业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.5018</td>\n",
       "      <td>450.1977</td>\n",
       "      <td>72.355329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>16423.0000</td>\n",
       "      <td>99718.0000</td>\n",
       "      <td>40809.896057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">房地产业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.0249</td>\n",
       "      <td>66.3147</td>\n",
       "      <td>10.849628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>15938.0000</td>\n",
       "      <td>120379.0000</td>\n",
       "      <td>45288.530466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">批发和零售业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.4833</td>\n",
       "      <td>102.7835</td>\n",
       "      <td>24.356891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>16073.0000</td>\n",
       "      <td>139627.0000</td>\n",
       "      <td>44120.035842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">教育业</th>\n",
       "      <th>人员</th>\n",
       "      <td>3.9384</td>\n",
       "      <td>127.4604</td>\n",
       "      <td>53.813387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>26175.0000</td>\n",
       "      <td>143215.0000</td>\n",
       "      <td>57414.487455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">文化、体育和娱乐业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.5999</td>\n",
       "      <td>19.0189</td>\n",
       "      <td>4.581411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>22377.0000</td>\n",
       "      <td>150810.0000</td>\n",
       "      <td>54176.698925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">水利、环境和公共设施管理业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.1636</td>\n",
       "      <td>18.2523</td>\n",
       "      <td>8.022701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>15831.0000</td>\n",
       "      <td>95341.0000</td>\n",
       "      <td>36995.433692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">电力、燃气及水的生产和供应业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.7362</td>\n",
       "      <td>32.1821</td>\n",
       "      <td>11.707497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>29419.0000</td>\n",
       "      <td>174252.0000</td>\n",
       "      <td>67744.182796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">科学研究、技术服务和地质勘查业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.6790</td>\n",
       "      <td>71.2481</td>\n",
       "      <td>11.614666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>26211.0000</td>\n",
       "      <td>176383.0000</td>\n",
       "      <td>66216.186380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">租赁和商务服务业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.1000</td>\n",
       "      <td>88.2695</td>\n",
       "      <td>12.672472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>16691.0000</td>\n",
       "      <td>156621.0000</td>\n",
       "      <td>43966.784946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">采矿业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.0523</td>\n",
       "      <td>103.0136</td>\n",
       "      <td>18.220504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>22732.0000</td>\n",
       "      <td>144454.0000</td>\n",
       "      <td>56747.756272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">金融业</th>\n",
       "      <th>人员</th>\n",
       "      <td>0.7717</td>\n",
       "      <td>54.4498</td>\n",
       "      <td>17.982375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>30627.0000</td>\n",
       "      <td>253637.0000</td>\n",
       "      <td>86977.516129</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                数据                           \n",
       "                               最小值          最大值           平均值\n",
       "行业              行业指标                                         \n",
       "交通运输、仓储和邮政业     人员          0.5997      85.3999     24.556320\n",
       "                人员平均工资  25098.0000  116763.0000  56241.240143\n",
       "住宿和餐饮业          人员          0.3000      39.3419      8.331891\n",
       "                人员平均工资  13455.0000   61095.0000  31198.731183\n",
       "信息传输、计算机服务和软件业  人员          0.2235      77.4400      9.204604\n",
       "                人员平均工资  22186.0000  212063.0000  72507.896057\n",
       "公共管理和社会组织       人员          7.8169     116.1762     50.301842\n",
       "                人员平均工资  25275.0000  128855.0000  55019.594982\n",
       "农林牧渔业           人员          0.3089      93.9292     10.091744\n",
       "                人员平均工资   8832.0000   74975.0000  30661.906810\n",
       "制造业             人员          0.6612    1020.2491    145.443355\n",
       "                人员平均工资  21508.0000  106835.0000  45198.247312\n",
       "卫生、社会保障和社会福利业   人员          1.3830      65.8223     24.421601\n",
       "                人员平均工资  22302.0000  169191.0000  59073.053763\n",
       "居民服务和其他服务业      人员          0.0557      11.0436      2.277055\n",
       "                人员平均工资  16022.0000   67013.0000  35929.476703\n",
       "建筑业             人员          0.5018     450.1977     72.355329\n",
       "                人员平均工资  16423.0000   99718.0000  40809.896057\n",
       "房地产业            人员          0.0249      66.3147     10.849628\n",
       "                人员平均工资  15938.0000  120379.0000  45288.530466\n",
       "批发和零售业          人员          0.4833     102.7835     24.356891\n",
       "                人员平均工资  16073.0000  139627.0000  44120.035842\n",
       "教育业             人员          3.9384     127.4604     53.813387\n",
       "                人员平均工资  26175.0000  143215.0000  57414.487455\n",
       "文化、体育和娱乐业       人员          0.5999      19.0189      4.581411\n",
       "                人员平均工资  22377.0000  150810.0000  54176.698925\n",
       "水利、环境和公共设施管理业   人员          0.1636      18.2523      8.022701\n",
       "                人员平均工资  15831.0000   95341.0000  36995.433692\n",
       "电力、燃气及水的生产和供应业  人员          0.7362      32.1821     11.707497\n",
       "                人员平均工资  29419.0000  174252.0000  67744.182796\n",
       "科学研究、技术服务和地质勘查业 人员          0.6790      71.2481     11.614666\n",
       "                人员平均工资  26211.0000  176383.0000  66216.186380\n",
       "租赁和商务服务业        人员          0.1000      88.2695     12.672472\n",
       "                人员平均工资  16691.0000  156621.0000  43966.784946\n",
       "采矿业             人员          0.0523     103.0136     18.220504\n",
       "                人员平均工资  22732.0000  144454.0000  56747.756272\n",
       "金融业             人员          0.7717      54.4498     17.982375\n",
       "                人员平均工资  30627.0000  253637.0000  86977.516129"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将简化后的字符进行再分组\n",
    "\n",
    "df_处理2.groupby([\"行业\",\"行业指标\"]).agg({\"数据\":[\"min\",\"max\",\"mean\"]}).rename(columns={'min':'最小值','max':'最大值','mean':'平均值'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 总结"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>在本周的课程中，通过对生肉数据进行“代号（字符）替换”、“数据切片”、“表格筛选”、“表格结合”等的操作，最终得到一个优雅且直观的数据表格。也充分认识到pandas在数据处理中的强大，对数据的处理能得到很多结果，这取决于我们怎么去运用pandas对其的操作。好好学习。</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
